﻿using HDS.Data;
using HDS.IService.Master;
using HDS.Model.Entity.View;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HDS.Service.Master
{
    public class StatisticService : IStatisticService
    {
        private MasterDbContext dbContext = new MasterDbContext();

        public async Task<List<VSaleStatistic>> GetAllSaleStatistic(DateTime date, string currency = "CNY")
        {
            var sql = "SELECT 'today_sales' type,COUNT(1) total_orders,SUM(DATEDIFF(checkout,checkin)) total_nights_rooms,currency, SUM(price) total_price";
            sql += " FROM `order_hotel_distributor`";
            sql += $" WHERE order_status = 10 AND created_time >= '{date.ToString("yyyy-MM-dd")}' AND created_time < '{date.AddDays(1).ToString("yyyy-MM-dd")}' GROUP BY currency";
            sql += " UNION";
            sql += " SELECT 'today_checkout_sales' type,COUNT(1) total_orders,SUM(DATEDIFF(checkout,checkin)) total_nights_rooms,currency, SUM(price) total_price";
            sql += " FROM `order_hotel_distributor`";
            sql += $" WHERE order_status = 10 AND checkout = '{date.ToString("yyyy-MM-dd")}' GROUP BY currency";
            sql += " UNION";
            sql += " SELECT 'month_sales' type,COUNT(1) total_orders,SUM(DATEDIFF(checkout,checkin)) total_nights_rooms,currency, SUM(price) total_price";
            sql += " FROM `order_hotel_distributor`";
            sql += $" WHERE order_status = 10 AND created_time >= '{date.ToString("yyyy-MM")}-01' AND created_time < '{date.AddMonths(1).ToString("yyyy-MM")}-01' GROUP BY currency";
            sql += " UNION";
            sql += " SELECT 'month_checkout_sales' type,COUNT(1) total_orders,SUM(DATEDIFF(checkout,checkin)) total_nights_rooms,currency, SUM(price) total_price";
            sql += " FROM `order_hotel_distributor`";
            sql += $" WHERE order_status = 10 AND checkout >= '{date.ToString("yyyy-MM")}-01-01' AND checkout < '{date.AddMonths(1).ToString("yyyy-MM")}-01' GROUP BY currency";
            sql += " UNION";
            sql += " SELECT 'year_sales' type,COUNT(1) total_orders,SUM(DATEDIFF(checkout,checkin)) total_nights_rooms,currency, SUM(price) total_price";
            sql += " FROM `order_hotel_distributor`";
            sql += $" WHERE order_status = 10 AND created_time >= '{date.ToString("yyyy")}-01-01' AND created_time < '{date.AddYears(1).ToString("yyyy")}-01-01' GROUP BY currency";
            sql += " UNION";
            sql += " SELECT 'total_sales' type,COUNT(1) total_orders,SUM(DATEDIFF(checkout,checkin)) total_nights_rooms,currency, SUM(price) total_price";
            sql += " FROM `order_hotel_distributor`";
            sql += " WHERE order_status = 10 GROUP BY currency";

            var exchangeRates = await dbContext.ExchangeRate.GetListAsync();
            var list = await dbContext.Db.SqlQueryable<VSaleStatistic>(sql).ToListAsync();
            var resultList = new List<VSaleStatistic>();
            foreach (var entity in list)
            {
                if (!entity.Currency.Equals(currency))
                {
                    var exchangeRate = exchangeRates.Where(e => e.CodeFrom.Equals(entity.Currency) && e.CodeTo.Equals(currency)).FirstOrDefault();
                    if (exchangeRate != null)
                    {
                        entity.Currency = currency;
                        entity.TotalPrice *= exchangeRate.Rate;
                    }
                }
            }
            var typeArr = list.Select(e => e.Type).Distinct().ToArray();
            foreach (var type in typeArr)
            {
                var _list = list.Where(e => e.Type.Equals(type));
                resultList.Add(new VSaleStatistic
                {
                    Type = type,
                    TotalOrders = _list.Sum(e => e.TotalOrders),
                    TotalNightsRooms = _list.Sum(e => e.TotalNightsRooms),
                    Currency = currency,
                    TotalPrice = _list.Sum(e => e.TotalPrice)
                });
            }

            return resultList;
        }

        public async Task<List<VSaleStatistic>> GetDailySaleStatistic(DateTime endDate, string currency = "CNY")
        {
           var sql = "SELECT STR_TO_DATE(DATE_FORMAT(created_time,'%Y-%m-%d'),'%Y-%m-%d') `date`,'daily' `type`,COUNT(1) total_orders,SUM(DATEDIFF(checkout,checkin)) total_nights_rooms,currency, SUM(price) total_price";
            sql += " FROM `order_hotel_distributor`";
            sql += $" WHERE order_status = 10 AND created_time >= '{endDate.AddDays(-30).ToString("yyyy-MM-dd")}' AND created_time < '{endDate.AddDays(1).ToString("yyyy-MM-dd")}' GROUP BY currency,DATE_FORMAT(created_time,'%Y-%m-%d')";

            var exchangeRates = await dbContext.ExchangeRate.GetListAsync();
            var list = await dbContext.Db.SqlQueryable<VSaleStatistic>(sql).ToListAsync();
            var resultList = new List<VSaleStatistic>();
            foreach (var entity in list)
            {
                if (!entity.Currency.Equals(currency))
                {
                    var exchangeRate = exchangeRates.Where(e => e.CodeFrom.Equals(entity.Currency) && e.CodeTo.Equals(currency)).FirstOrDefault();
                    if (exchangeRate != null)
                    {
                        entity.Currency = currency;
                        entity.TotalPrice *= exchangeRate.Rate;
                    }
                }
            }
            var dateArr = list.Select(e => e.Date).Distinct().ToArray();
            foreach (var date in dateArr)
            {
                var _list = list.Where(e => e.Date.Equals(date));
                resultList.Add(new VSaleStatistic
                {
                    Date = date,
                    Type = "",
                    TotalOrders = _list.Sum(e => e.TotalOrders),
                    TotalNightsRooms = _list.Sum(e => e.TotalNightsRooms),
                    Currency = currency,
                    TotalPrice = _list.Sum(e => e.TotalPrice)
                });
            }

            return resultList;
        }
    }
}
